Skip Headers

Oracle® Database Platform Guide 10g Release 2 (10.2) for Microsoft Windows (32-Bit) Part Number B14304-03 |
Home | Book List | Contents | Index | Master Index | Feedback

Previous | Next
| View PDF

# 5 Administering a Database on Windows

This chapter describes how to administer Oracle Database for Windows.

This chapter contains these topics:

- Managing Oracle Database Services

- Starting and Shutting Down a Database with SQL*Plus

- Starting and Shutting Down a Database Using Services

- Starting Multiple Instances

- Creating and Populating Password Files

- Connecting Remotely to the Database as SYS

- Automatically Encrypted Database Passwords

- Creating Control, Data, and Log Files on Remote Computers

- Archiving Redo Log Files

## Managing Oracle Database Services

This section tells you how to manage the services that Oracle Database installs on your computer.

This section provides information on the following:

- Oracle Database Service Naming Conventions for Multiple Oracle Homes

- Starting Oracle Database Services

- Stopping Oracle Database Services

- Auto-starting Oracle Database Services

### Oracle Database Service Naming Conventions for Multiple Oracle Homes

Oracle Database for Windows lets you have multiple Oracle homes on a single computer. This feature, described in Appendix B, "Optimal Flexible Architecture", in Oracle Database Installation Guide for Microsoft Windows (32-Bit), affects Oracle services naming conventions. As you perform installations into Oracle home directories:

- You must accept default Oracle home name provided or specify a different name for each Oracle home directory.

- You are prompted to give a system identifier and global database name for each database installation.

### Starting Oracle Database Services

Oracle Database services must be started for you to use Oracle Database and its products. You can start Oracle Database services from three different locations:

- Control Panel

- Command Prompt

- Oracle Administration Assistant for Windows

  Note: You can start Oracle Database when you start `OracleService` `SID`. See "Starting and Shutting Down a Database Using Services" for information on registry parameters that enable you to do this.

Control Panel

To start Oracle Database services from the Control Panel:

1. Access your Windows Services dialog.

   See Also: Your operating system documentation for instructions

2. Find the service to start in the list, select it, and click Start.

   If you cannot find `OracleService` `SID` in the list, then use ORADIM to create it.

3. Click Close to exit the Services dialog.

Command Prompt

To start Oracle Database services from the command prompt, enter:

    C:\> NET START service

where `service` is a specific service name, such as `OracleServiceORCL.`

Oracle Administration Assistant for Windows

To start Oracle Database services from Oracle Administration Assistant for Windows:

1. Choose Start `>` Programs `>` Oracle - HOME_NAME `>` Configuration and Migration Tools > Administration Assistant for Windows

2. Right-click the `SID`.

   where `SID` is a specific instance name, such as `orcl`.

3. Click Start Service.

   This starts service `OracleServiceORCL`.

### Stopping Oracle Database Services

On occasion (for example, when re-installing Oracle Database), you must stop Oracle Database services. You can stop Oracle Database services from three different locations:

- Control Panel

- Command Prompt

- Oracle Administration Assistant for Windows

  Note: You can stop Oracle Database in normal, immediate, or abort mode when you stop `OracleService` `SID`. See "Starting and Shutting Down a Database Using Services" for information on registry parameters that enable you to do this.

Control Panel

To stop Oracle Database services from the Control Panel:

1. Access your Windows Services dialog.

   See Also: Your operating system documentation for instructions

2. Select `Oracle` `HOME_NAME` `TNSListener` and click Stop.

   `Oracle` `HOME_NAME` `TNSListener` is stopped.

3. Select `OracleService` `SID` and click Stop.

4. Click OK.

   `OracleService` `SID` is stopped.

Command Prompt

To stop Oracle Database services from the command prompt, enter:

    C:\> net STOP service

where `service` is a specific service name, such as `OracleServiceORCL`.

Oracle Administration Assistant for Windows

To stop Oracle Database services from Oracle Administration Assistant for Windows:

1. Choose Start `>` Programs > Oracle - HOME_NAME `>` Configuration and Migration Tools `>` Administration Assistant for Windows.

2. Right-click the SID.

   where `SID` is a specific instance name, such as `orcl`.

3. Click Stop Service.

   This stops service OracleServiceORCL.

### Auto-starting Oracle Database Services

Oracle Database services can be set to start automatically whenever the Windows computer is restarted. You can turn auto-start on or off from two different locations:

- Control Panel

- Oracle Administration Assistant for Windows

Control Panel

To use the Control Panel to configure when and how Oracle Database is started:

1. Access your Windows Services dialog.

   See Also: Your operating system documentation for instructions

2. Select service OracleServiceSID and click Startup.

3. Choose Automatic from the Startup Type field.

4. Click OK.

5. Click Close to exit the Services dialog.

Oracle Administration Assistant for Windows

To automatically start Oracle Database services from Oracle Administration Assistant for Windows:

1. Choose Start `>` Programs `>` Oracle - HOME_NAME `>` Configuration and Migration Tools `>` Administration Assistant for Windows.

2. Right-click the SID.

   where `SID` is a specific instance name, such as `orcl`.

3. Choose Startup/Shutdown Options.

4. Choose the Oracle Service tab.

5. Choose Automatic in Oracle Service Startup Type.

6. Click Apply.

7. Click OK.

Description of the illustration ss_cnfg1.gif

## Starting and Shutting Down a Database with SQL*Plus

These instructions assume that a database instance has been created.

Note: Directory path examples in this chapter follow Optimal Flexible Architecture (OFA) guidelines. If you specified non-OFA compliant directories during installation, then your directory paths will differ. See Appendix B, "Optimal Flexible Architecture", in Oracle Database Installation Guide for Microsoft Windows (32-Bit) for more information.

To start or shut down Oracle Database:

1. Go to your Oracle Database server.

2. Start SQL*Plus at the command prompt:

       C:\> sqlplus /NOLOG

3. Connect to Oracle Database with username SYSDBA:

       SQL> CONNECT / AS SYSDBA

4. To start a database, enter:

       SQL> STARTUP [PFILE=path\filename]

   This command uses the initialization parameter file specified in `path``\``filename`. To start a database using a file named `init2.ora` located in

       C:oracle\product\10.2.0\admin\orcl\pfile

   you would enter:

       SQL> STARTUP PFILE=C:oracle\product\10.2.0\admin\orcl\pfile\init2.ora

   If no `PFILE` is specified, then the command looks for an `SPFILE` in `ORACLE_HOME``\database`. If the command finds one, then the command uses it to start the database. If it does not find an `SPFILE`, then it uses the default initialization parameter file located in `ORACLE_BASE``\ADMIN\db_name\pfile`.

5. To stop a database, enter:

       SQL> SHUTDOWN [mode]

   where mode is `normal`, `immediate`, or `abort`.

   In a `normal` shutdown, Oracle Database waits for all currently-connected users to disconnect and disallows any new connections before shutting down. This is the default mode.

   In an `immediate` shutdown, Oracle Database terminates and rolls back active transactions, disconnects clients, and shuts down.

   In an `abort` shutdown, Oracle Database terminates active transactions and disconnects users; it does not roll back transactions. The database performs automatic recovery and rollback the next time it is started. Use this mode only in emergencies.

   See Also: Chapter 2, "Database Tools on Windows" for a list of other tools that can start Oracle Database and this guide for information on options you can specify when starting your database.

## Starting and Shutting Down a Database Using Services

You can start or shut down Oracle Database by starting or stopping service `OracleService` `SID` in the Control Panel. Starting `OracleService` `SID` is equivalent to using the `STARTUP` command or manually entering:

    C:\> oradim -STARTUP -SID SID [-STARTTYPE srvc | inst | srvc,inst] [-PFILE
    filename | -SPFILE]

Stopping `OracleService` `SID` is equivalent to using the `SHUTDOWN` command or manually entering:

    C:\> oradim -SHUTDOWN -SID SID [-SHUTTYPE srvc | inst | srvc,inst] [-SHUTMODE
    normal | immediate | abort]

You can enable starting and stopping Oracle Database through `OracleService` `SID` two different ways:

- Oracle Administration Assistant for Windows

- Setting Registry Parameters

Oracle Administration Assistant for Windows

To start or stop a database using Oracle Database services from Oracle Administration Assistant for Windows:

1. Choose Start `>` Programs `>` Oracle - HOME_NAME `>` Configuration and Migration Tools `>` Administration Assistant for Windows.

2. Right-click the SID.

   where `SID` is a specific instance name, such as `ORCL`.

3. Choose Startup/Shutdown Options.

4. Choose the Oracle Instance tab.

5. Select Start up instance when service is started, Shut down instance when service is stopped, or both.

Description of the illustration ss_cnfg2.gif

Setting Registry Parameters

To start or stop Oracle Database through Oracle Database Services, set the following registry parameters to the indicated values:

- `ORA_``SID``_AUTOSTART`

  When set to `true`, the default value, this parameter causes Oracle Database to start when `OracleService` `SID` is started.

- `ORA_``SID``_PFILE`

  This parameter sets the full path to the initialization parameter file. If this entry is not present, then oradim will try to start the database with an `SPFILE` or `PFILE` from `ORACLE_HOME``\database`.

- `ORA_SHUTDOWN`

  When set to `true`, this parameter enables the selected instance of Oracle Database to be shut down when `OracleService` `SID` is stopped. This includes any database in the current Oracle home. Default value is `false`.

- `ORA_``SID``_SHUTDOWN`

  When set to `true`, the default value, this parameter causes the instance of Oracle Database identified by the `SID` value to shut down when `OracleService` `SID` is stopped manually—using either the Control Panel or Net stop command.

  Caution: If `ORA_SHUTDOWN` or `ORA_SID_SHUTDOWN` is set to `false`, then manually shutting down OracleServiceSID will still shut down Oracle Database. But it will be an abnormal shutdown, and Oracle does not recommend it.

The following two registry parameters are optional:

- `ORA_``SID``_SHUTDOWNTYPE`

  This parameter controls database shutdown mode. Set it to `a` (`abort`), `i` (`immediate`), or `n` (`normal`). Default mode is `i` (`immediate`) if you do not set this parameter.

- `ORA_``SID``_SHUTDOWN_TIMEOUT`

  This parameter sets maximum time to wait before the service for a particular `SID` stops.

The registry location of these required and optional parameters is determined by the number of Oracle home directories on your computer. If you have only one Oracle home directory, then these parameters belong in:

    HKEY_LOCAL_MACHINE\SOFTWARE\ORACLE\HOME0

If you have multiple Oracle home directories, then these parameters belong in:

    HKEY_LOCAL_MACHINE\SOFTWARE\ORACLE\HOMEID

where `ID` is incremented for each additional Oracle home directory on your computer.

Note: If you use ORADIM to create or edit instances, then it automatically sets the relevant registry parameters to their appropriate values.

See Also: Chapter 14, "Configuration Parameters and the Registry" for instructions on adding and editing registry parameters

Starting or Stopping OracleServiceSID from the Control Panel

1. To start the database, start OracleService SID.

   This automatically starts ORADIM and issues the -`STARTUP` command using the initialization parameter file identified by `ORA_``SID``_PFILE`.

2. To stop the database, stop OracleService SID.

   This automatically starts ORADIM, which issues the `-SHUTDOWN` command in the mode indicated by `ORA_``SID``_SHUTDOWNTYPE`, and shuts down Oracle Database.

   See Also: Your operating system documentation for instructions on starting and stopping services.

## Starting Multiple Instances

1. Start the service for each instance using ORADIM or the Services dialog of the Control Panel.

2. At the command prompt set the `ORACLE_SID` configuration parameter to the `SID` for the first instance to run:

       C:\> SET ORACLE_SID=SID

   where `SID` is the name of the Oracle Database instance.

3. Start SQL*Plus:

       C:\> sqlplus /NOLOG

4. Connect `AS` `SYSDBA`:

       SQL> CONNECT / AS SYSDBA

5. Start up the first instance:

       SQL> STARTUP PFILE=ORACLE_BASE\admin\db_name\pfile\init.ora

   where `ORACLE_BASE` is `c:\oracle\product\10.2.0` (unless you changed it during installation) and `db_name` is the name of the instance.

6. Repeat Steps 2-5 for the other instances to run.

## Creating and Populating Password Files

Use Password Utility to create password files. Password Utility is automatically installed with Oracle Database utilities. Password files are located in directory `ORACLE_BASE``\``ORACLE_HOME``\DATABASE` and are named `PWD` `sid``.ORA`, where `SID` identifies the Oracle Database instance. Password files can be used for local or remote connections to Oracle Database.

To create and populate a password file:

1. Create a password file with the Password Utility:

       C:\> orapwd FILE=PWDsid.ora PASSWORD=password ENTRIES=max_users

   where

   - `FILE` specifies the password filename.

   - `SID` identifies the database instance.

   - `PASSWORD` sets the password for account `SYS`.

   - `ENTRIES` sets maximum number of entries in password file. This corresponds to maximum number of distinct users allowed to connect to the database simultaneously with either the SYSDBA or the SYSOPER DBA privilege.

2. Set initialization parameter file parameter `REMOTE_LOGIN_PASSWORDFILE` to `exclusive`, `shared`, or `none`.

   The value `exclusive` specifies that only one instance can use the password file and that the password file contains names other than `SYS`. In search of the password file, Oracle Database looks in the registry for the value of parameter `ORA_SID_PWFILE`. If no value is specified, then it looks in the registry for the value of parameter `ORA_PWFILE`, which points to a file containing usernames, passwords, and privileges. If that is not set, then it uses the default:

       ORACLE_BASE\ORACLE_HOME\DATABASE\PWDsid.ORA.

   The default value is `shared`. It specifies that multiple instances (for example, a Real Application Clusters environment) can use the password file. However, the only user recognized by the password file is `SYS`. Other users cannot log in with `SYSOPER` or `SYSDBA` privileges even if those privileges are granted in the password file. The `shared` value of this parameter affords backward compatibility with earlier Oracle releases. Oracle Database looks for the same files as it does when the value is `exclusive`.

   The value `none` specifies that Oracle Database ignores the password file and that authentication of privileged users is handled by the Windows operating system.

3. Start SQL*Plus:

       C:\> sqlplus /NOLOG

4. Connect `AS SYSDBA`:

       SQL> CONNECT / AS SYSDBA

5. Start Oracle Database:

       SQL> STARTUP

6. Grant appropriate privileges to each user. Users who must perform database administration, for example, would be granted privilege `SYSDBA`:

       SQL> GRANT SYSDBA TO db_administrator;

   If the grant is successful, then the following message displays:

       Statement Processed.

   This adds `scott` to the password file and enables `scott` to connect to the database with `SYSDBA` privileges. Use SQL*Plus to add or delete usernames, user passwords, and user privileges in password files.

   Caution: Copying or manually moving password files may result in ORADIM being unable to find a password to start an instance.

### Viewing and Hiding the Password File

The password file is not automatically hidden. It can be made invisible and visible again from two different locations:

- Command Prompt

- Windows Explorer

  Note: The password file must be visible before it can be moved, copied, or deleted.

Command Prompt

1. To see the password file, enter:

       ORACLE_BASE\ORACLE_HOME\database> attrib

   The password file is displayed as `PWD` `sid``.ora`:

       A       ORACLE_BASE\ORACLE_HOME\database\oradba.exe
       A       ORACLE_BASE\ORACLE_HOME\database\OraDim.Log
       A       ORACLE_BASE\ORACLE_HOME\database\PWDsid.ora
       A       ORACLE_BASE\ORACLE_HOME\database\SPFILEsid.ora

2. To make the password file invisible, enter:

       ORACLE_BASE\ORACLE_HOME\database> attrib +H PWDsid.ora

3. To see the effect of the change, enter:

       ORACLE_BASE\ORACLE_HOME\database> attrib

   The password file is now hidden:

       A       ORACLE_BASE\ORACLE_HOME\database\oradba.exe
       A       ORACLE_BASE\ORACLE_HOME\database\OraDim.Log
       A   H   ORACLE_BASE\ORACLE_HOME\database\PWDsid.ora
       A       ORACLE_BASE\ORACLE_HOME\database\SPFILEsid.ora

4. To make the password file visible again, enter:

       ORACLE_BASE\ORACLE_HOME\database> attrib -H PWDsid.ora

Windows Explorer

To make the password file invisible or visible again:

1. Navigate to directory `ORACLE_BASE``\``ORACLE_HOME``\database`.

2. Right-click PWD sid.ora.

3. Choose Properties.

   The `PWD` `sid``.ora` Properties dialog opens.

4. In Attributes, check or clear the checkbox next to Hidden.

5. Click OK.

To view or hide an invisible password file:

1. Navigate to directory `ORACLE_BASE``\``ORACLE_HOME``\database`.

2. Choose Folder Options from the View main menu.

3. Choose the View tab.

4. To view an invisible password file, choose Show hidden files and folders.

5. To hide a visible password file, choose Do not show hidden files and folders.

6. Click OK.

## Connecting Remotely to the Database as SYS

When connecting to the starter database from a remote computer as `SYS`, you must use a different password from the one described in Oracle Database Installation Guide for Microsoft Windows (32-Bit) when logging on with `SYSDBA` privileges. This is because the password file enables database access in this situation and it requires the password `oracle` for this purpose.

## Automatically Encrypted Database Passwords

With Oracle Database, the password used to verify a remote database connection is automatically encrypted. Whenever a user attempts a remote login, Oracle Database encrypts the password before sending it to the remote database. If the connection fails, then the failure is noted in the operating system audit log.

Note: Configuration parameter `ORA_ENCRYPT_LOGIN` is retained for backward compatibility and is set to `true` by default. See Chapter 14, "Configuration Parameters and the Registry" for instructions on adding and setting configuration parameters in the registry.

## Creating Control, Data, and Log Files on Remote Computers

Oracle Database can access database files on a remote computer using Universal Naming Convention (UNC), but it may degrade database performance and network reliability. UNC is a PC format for specifying locations of resources on a local area network. UNC uses the following format:

    \\server-name\shared-resource-path-name

For example, UNC specification for file `system01.dbf` in directory `C:\oracle\product\10.2.0\oradata\orcl` on shared server `argon` would be:

    \\argon\oracle\product\10.2.0\oradata\orcl\system01.dbf

Locations of archive log files cannot be specified using UNC. Always set initialization parameter `LOG_ARCHIVE_DEST_``n` to a mapped drive. If you set it to a UNC specification, then Oracle Database does not start and you receive the following errors:

    ORA-00256: error occurred in translating archive text string '\meldell\rmdrive'
    ORA-09291: sksachk: invalid device specified for archive destination
    OSD-04018: Unable to access the specified directory or device
    O/S-Error: (OS 2) The system cannot find the file specified

An ORA-00256 error also occurs if you enter `\\\meldell\rmdrive` or `\\\meldell\\rmdrive`. Control files required the additional backslashes for Oracle8 release 8.0.4, but redo log files and datafiles did not.

## Archiving Redo Log Files

If you installed Oracle Database through the Typical installation, then it is created in `NOARCHIVELOG` mode. If you created your database through the Custom option of Database Configuration Assistant, then you had the choice of either `ARCHIVELOG` or `NOARCHIVELOG`.

In `NOARCHIVELOG` mode, redo logs are not archived. Setting your archive mode to `ARCHIVELOG` and enabling automatic archiving causes redo log files to be archived. This protects Oracle Database from both instance and disk failure.

This section describes how to change archive mode to `ARCHIVELOG` and enable automatic archiving.

See Also: For complete descriptions of `ARCHIVELOG` and `NOARCHIVELOG` modes, see "Managing Archived Redo Logs" in Oracle Database Administrator's Guide

### Changing Archive Mode to ARCHIVELOG

1. Start SQL*Plus at the command prompt:

       C:\> sqlplus /NOLOG

2. Connect to Oracle Database as `SYSDBA`:

       SQL> CONNECT / AS SYSDBA

3. If the database is open, then shut it down:

       SQL> SHUTDOWN

4. Next, mount the database:

       SQL> STARTUP MOUNT

5. Enter the following command:

       SQL> ARCHIVE LOG LIST

   The following output indicates the database is not in archive mode:

       Database log mode              No Archive Mode
       Automatic archival             Disabled
       Archive destination            %RDBMS%\
       Oldest online log sequence     34
       Current log sequence           37

6. Change archive mode to `ARCHIVELOG`:

       SQL> ALTER DATABASE ARCHIVELOG;

7. Enter the following command:

       SQL> ARCHIVE LOG LIST

   The following output indicates the database is now in archive mode:

       Database log mode              Archive Mode
       Automatic archival             Disabled
       Archive destination            %RDBMS%\
       Oldest online log sequence     34
       Current log sequence           37

8. Open the database:

       SQL> ALTER DATABASE OPEN;

### Enabling Automatic Archiving

1. Open file `ORACLE_BASE``\ADMIN\``db_name``\pfile\init.ora`.

2. Find the following three initialization parameters:

       # LOG_ARCHIVE_START = true
       # LOG_ARCHIVE_DEST_1 = %ORACLE_HOME%\database\archive
       # LOG_ARCHIVE_FORMAT = "%%ORACLE_SID%%T%TS%S.ARC"

   Note: The `LOG_ARCHIVE_DEST_n` parameter can specify standby destinations that are running Oracle Standard Edition, but only when specifying local destinations with the `LOCATION` attribute. See "Oracle Software Requirements" in Oracle Data Guard Concepts and Administration for details.

3. Remove the `#` sign from in front of each.

   Note: Double quotation marks around `LOG_ARCHIVE_FORMAT`

4. Edit the `LOG_ARCHIVE_DEST_``n` value to identify an existing drive and directory in which to archive your filled redo logs.

   Note: Default size of Redo logs is approximately 100 MB.

5. Edit the `LOG_ARCHIVE_FORMAT` value to indicate the appropriate archiving format:

   - `%%``ORACLE_SID``%%%T.ARC`

     This format specifies the thread number. This number is padded to the left by zeroes. The default value is one with a range of up to three characters. Example: `SID0001.ARC`

   - `%%``ORACLE_SID``%%%S.ARC`

     This format specifies the log sequence number. This number is padded to the left by zeroes. The default value is one with a range of up to five characters. Example: `SID0001.ARC`

   - `%%``ORACLE_SID``%%%t.ARC`

     This format specifies the thread number. The number is not padded. The default value is one with no range limit on characters. Example: `SID1.ARC`

   - `%%``ORACLE_SID``%%%s.ARC`

     This format specifies the log sequence number. The number is not padded. The default value is one with no range limit on characters. Example: `SID1.ARC`

6. Save your changes.

7. Exit the file.

8. Shut down the database:

       SQL> SHUTDOWN

9. Restart the database

       SQL> STARTUP

10. Enter the following command:

        SQL> ARCHIVE LOG LIST

    The following output indicates that automatic archiving of redo log files is enabled and an archiving destination is specified:

        Database log mode              Archive Mode
        Automatic archival             Enabled
        Archive destination            C:\BACKUP
        Oldest online log sequence     34
        Current log sequence           37